Imports System.Data.OleDb

''' <summary>
''' Paradox provider
''' </summary>
''' <remarks>
''' Although this relies on OleDB, the BDE is still required to perform most functions (although my own library can be used to import from .DB without BDE)
''' The BDE can be installed using two files in the Redist folder: BdeInst.dll and MiniReg.exe. To install BDE, add the following to the installer:
''' [Run]
''' Filename: {tmp}\MiniReg.exe; Parameters: """{tmp}\BdeInst.dll"""
''' (or create a batch file to do the same thing)
''' </remarks>
Public Class clsDBParadox
    Inherits clsDBOleDB

    Public Sub New()
        DatabaseType = enumDBType.Paradox
    End Sub

    ''' <summary>
    ''' Creates a provider-specific connection object given previously specified properties as required (DataSource, DatabaseName, UserName, Password, etc.)
    ''' Note that the connection is normally automatically created when you instantiate the class
    ''' </summary>
    Public Overrides Sub CreateConnection()
        'note: Paradox access requires that the BDE be installed
        If My.Computer.Registry.GetValue("HKEY_LOCAL_MACHINE\SOFTWARE\BORLAND\DATABASE ENGINE", "CONFIGFILE01", "") = "" Then
            Connection = Nothing
            Throw New ApplicationException("This project utilizes Paradox tables, however it does not appear that the Borland Database Engine is installed on this computer. Please run SetupBDE.bat in the WRDB program folder. You will need Adminitrator rights to do this; if you wish to run WRDB using limited permissions (non-Administrator), you MUST configure the Borland Database Engine's NET DIR variable to an existing directory other than C:\")
        End If
        Dim csb As New OleDbConnectionStringBuilder
        With csb
            .Provider = "Microsoft.Jet.OLEDB.4.0"
            .Item("Extended Properties") = "Paradox 7.x"
            .DataSource = DataSource 'should be working directory; must reset when doing queries on support or master tables
            Connection = New OleDbConnection(.ConnectionString)
        End With
    End Sub

    ''' <summary>
    ''' Determine if table exists in the database
    ''' </summary>
    ''' <param name="TableName">Name of table</param>
    ''' <returns>True if exists</returns>
    ''' <remarks>Override for Paradox</remarks>
    Public Overrides Function TableExists(ByVal TableName As String) As Boolean
        Return My.Computer.FileSystem.FileExists(String.Format("{0}\{1}.db", DataSource, RemoveBrackets(TableName)))
    End Function

    ''' <summary>
    ''' Rename table in current project
    ''' </summary>
    ''' <param name="OldName">Name of existing table (must already exist)</param>
    ''' <param name="NewName">New name for table (must not already exist)</param>
    ''' <param name="dtTemplate">Only needed for Firebird override since it can't to a rename</param>
    ''' <returns>True if successful</returns>
    ''' <remarks>Must override for SQL Server, Access, and Firebird</remarks>
    Public Overrides Function RenameTable(ByVal OldName As String, ByVal NewName As String, Optional ByVal dtTemplate As DataTable = Nothing) As Boolean
        If TableExists(NewName) Or Not TableExists(OldName) Then Return False
        For Each fn As String In My.Computer.FileSystem.GetFiles(DataSource, FileIO.SearchOption.SearchTopLevelOnly, RemoveBrackets(OldName) & ".*")
            Dim NewFileName As String = IO.Path.GetFileName(fn.ToUpper.Replace(RemoveBrackets(OldName.ToUpper), RemoveBrackets(NewName)))
            My.Computer.FileSystem.RenameFile(fn, NewFileName)
        Next
        Return True
    End Function

    ''' <summary>
    ''' Creates new database on the provider (uses supervisor access)
    ''' </summary>
    ''' <param name="DatabaseName">Desired name of new database</param>
    ''' <returns>True if successful</returns>
    Public Overrides Function CreateDatabase(ByRef DatabaseName As String) As Boolean
        'cannot be done in Paradox
        Return False
    End Function

    ''' <summary>
    ''' Perform database compaction/repair operation where supported
    ''' </summary>
    Public Overrides Sub RepairDatabase(Optional ByVal Interactive As Boolean = False)
        Environment.CurrentDirectory = IO.Path.GetDirectoryName(Reflection.Assembly.GetExecutingAssembly.Location)
        Dim Util As String = "Pdxrbld.exe"
        Dim LogFile As String = "Pdxrbld.log"
        If Not My.Computer.FileSystem.FileExists(Util) Then Throw New ApplicationException(String.Format("The required utility could not be found: {0}", Util)) : Exit Sub
        If Interactive Then
            Shell(Util)
        Else
            Shell(String.Format("{0} /F{1} -R1 -L+ -Q+", Util, GetShortName(DataSource)), AppWinStyle.NormalFocus, True)
        End If
    End Sub

    ''' <summary>
    ''' Return a list of  tables for this project
    ''' </summary>
    ''' <returns>List of table names</returns>
    Public Overrides Function GetTableList() As Generic.List(Of String)
        Try
            Dim col As New Generic.List(Of String)
            For Each f As String In My.Computer.FileSystem.GetFiles(DataSource, FileIO.SearchOption.SearchTopLevelOnly, "*.db")
                col.Add(Proper(IO.Path.GetFileNameWithoutExtension(f)))
            Next
            Return col
        Catch ex As Exception
            Throw
            Return Nothing
        End Try
    End Function

    ''' <summary>
    ''' Import data from tab-delimited file into table; if table exists, will be emptied first (or updated), if not will be created
    ''' The file format is assumed to be identical to the export format
    ''' </summary>
    ''' <param name="TableName">New table to import data into</param>
    ''' <param name="ImportFileName">Tab-delimited text file to import data from</param>
    ''' <param name="dtTemplate">Data table containing structure of table to import to</param> 
    ''' <param name="Append_Update">If false, existing table will be emptied first; otherwise will be appended to or replaced on record-by-record basis (if indexed)</param>
    ''' <param name="AppendOnly">If Append_Update is true and AppendOnly is true, will not replace existing records with new records (this is ignored for Paradox)</param>
    ''' <param name="MustBeUpdateable">Set this to True only if creating data table and is Paradox, dBase, etc.</param>
    ''' <returns>True if successful</returns>
    Public Overrides Function ImportTable(ByRef TableName As String, ByVal ImportFileName As String, ByVal dtTemplate As DataTable, Optional ByVal Append_Update As Boolean = False, Optional ByVal AppendOnly As Boolean = False, Optional ByVal MustBeUpdateable As Boolean = True) As Boolean

        Dim TempTable As String = "Temp_Import"
        Try
            'this routine will import to a non-indexed paradox table, then append to existing indexed table if requested

            Cancelled = False

            If Not My.Computer.FileSystem.FileExists(ImportFileName) Then Return False

            TableName = GetValidTableName(TableName)
            If TableName = "" Then Return False

            Dim StartTime As Integer = My.Computer.Clock.TickCount

            If Not Append_Update Then 'overwrite it
                DropTable(TableName)
                'If Not CreateTable(TableName, dtTemplate, MustBeUpdateable) Then Return False
            Else 'create if doesn't exist
                If Not TableExists(TableName) Then CreateTable(TableName, dtTemplate, True)
            End If

            Dim LineNum As Integer = 1

            MyBase.RaiseProgress(0, 100, String.Format("Importing data into {0}...", TableName))

            Dim NumRecords As Integer

            ''if has "autoincrement" primary key (e.g., data table, which is actually manually incremented), determine next ID to use so can pass to import routines

            Dim AutoIncStart As Integer = 0
            'If GetColumnList(TableName).Contains("RECID") Then
            '    If GetRecordCount(TableName) = 0 Then
            '        AutoIncStart = 1
            '    Else
            '        AutoIncStart = TestNull(ExecuteScalar("SELECT MAX(RecID) FROM {0}", TableName), 0) + 1
            '    End If
            'End If

            'if destination is indexed then create a new temporary non-indexed data table that will be imported to 

            Dim TempFile As String = String.Format("{0}\{1}.db", DataSource, TempTable)

            DropTable(TempTable)
            If Not CreateTable(TempTable, dtTemplate, False) Then Return False

            If Not ImportFromText(TempFile, ImportFileName, NumRecords, AutoIncStart) Then Return False

            'if want to append imported data to table, do so next; also if table must have primary key to be updateable, insert into indexed table
            If Append_Update Or MustBeUpdateable Then
                MyBase.RaiseProgress(0, 100, String.Format("Appending to table {0}...", TableName))
                Try
                    Return AppendTable(TableName, TempTable)
                Catch ex As Exception
                    'automatically import record-by-record, so can get error messages
                    Return MyBase.ImportTable(TableName, ImportFileName, dtTemplate, Append_Update, AppendOnly, MustBeUpdateable)
                End Try
            Else
                If Not RenameTable(TempTable, TableName) Then Return False
            End If

            Return True

        Catch ex As Exception
            Throw New DbSqlExceptionPDox("Error occurred while trying to import to Paradox.", ex)
        Finally
            DropTable(TempTable)
            MyBase.RaiseProgress(100, 100, "")
        End Try
    End Function

    ''' <summary>
    ''' Create an empty Paradox file of the proper schema
    ''' </summary>
    ''' <param name="PDoxFileName">Full path to paradox file to be create (existing file will be deleted)</param>
    ''' <param name="dtTemplate">Schema</param>
    ''' <returns>True if successful</returns>
    ''' <remarks>This was created so can export to Paradox without BDE</remarks>
    Shared Function CreateParadoxFile(PDoxFileName As String, dtTemplate As DataTable) As Boolean
        Dim Hdr As PDoxHeader = Nothing
        Dim Hdr2 As PDoxHeader2 = Nothing
        Dim fs As IO.FileStream = Nothing
        Dim bw As IO.BinaryWriter = Nothing

        Try
            Dim FieldList As New Generic.List(Of clsParadoxField)

            For Each col As DataColumn In dtTemplate.Columns
                Dim pdoxField As clsParadoxField = Nothing
                With col
                    If .DataType Is GetType(String) Then
                        pdoxField = New clsParadoxField(.ColumnName, enumFieldType.pxfAlpha, .MaxLength)
                    ElseIf .DataType Is GetType(Double) Or .DataType Is GetType(Single) Or .DataType Is GetType(Decimal) Then
                        pdoxField = New clsParadoxField(.ColumnName, enumFieldType.pxfNumber)
                    ElseIf .DataType Is GetType(Integer) Or .DataType Is GetType(Int64) Or .DataType Is GetType(Int16) Or .DataType Is GetType(Int32) Then
                        pdoxField = New clsParadoxField(.ColumnName, enumFieldType.pxfLong)
                    ElseIf .DataType Is GetType(Boolean) Then
                        pdoxField = New clsParadoxField(.ColumnName, enumFieldType.pxfLogical)
                    ElseIf .DataType Is GetType(Date) Then
                        pdoxField = New clsParadoxField(.ColumnName, enumFieldType.pxfTimestamp)
                    End If
                    FieldList.Add(pdoxField)
                End With
            Next

            'delete the file if it already exists
            If My.Computer.FileSystem.FileExists(PDoxFileName) Then My.Computer.FileSystem.DeleteFile(PDoxFileName)

            InitializeHeader(IO.Path.GetFileName(PDoxFileName.ToUpper), FieldList, Hdr, Hdr2)

            fs = New IO.FileStream(PDoxFileName, IO.FileMode.CreateNew, IO.FileAccess.Write, IO.FileShare.Write, 1024)
            bw = New IO.BinaryWriter(fs)

            'create a paradox file that just contains header information (no records)

            Return PutHeader(bw, Hdr, Hdr2)

        Catch ex As Exception
            Throw
            Return False
        Finally
            If bw IsNot Nothing Then bw.Close()
            If fs IsNot Nothing Then fs.Close() : fs.Dispose()
        End Try
    End Function

    Public Shared Function ImportFromText(PDoxFileName As String, ImportFileName As String, ByRef NumRec As Integer, Optional ByVal AutoIncStart As Integer = 0) As Boolean
        Dim fs As IO.FileStream = Nothing
        Dim br As IO.BinaryReader = Nothing
        Dim bw As IO.BinaryWriter = Nothing
        Dim sr As IO.StreamReader = Nothing
        Dim Hdr As PDoxHeader = Nothing
        Dim Hdr2 As PDoxHeader2 = Nothing
        NumRec = 0

        Try
            fs = New IO.FileStream(PDoxFileName, IO.FileMode.Open, IO.FileAccess.ReadWrite, IO.FileShare.Write, 1024)
            br = New IO.BinaryReader(fs)
            bw = New IO.BinaryWriter(fs)
            sr = New IO.StreamReader(ImportFileName)

            'read header

            If Not GetHeader(br, Hdr, Hdr2) Then Return False

            With Hdr

                Dim ar() As String = sr.ReadLine.Split(vbTab)

                'do minimal mapping of column headings to field names so missing or extra or out-of-order columns in text file are handled properly

                'convert column headings and field names to upper case
                For i As Integer = 0 To Hdr2.fieldName.Length - 1
                    Hdr2.fieldName(i) = Hdr2.fieldName(i).ToUpper
                Next

                For i As Integer = 0 To ar.Length - 1
                    If ar(i).Length > 25 Then ar(i) = ar(i).Substring(0, 25) 'only compare the first 25 characters (PDOX limit)
                    ar(i) = ar(i).ToUpper
                Next

                'determine ordinal value (field number) of table column for each field in import file

                Dim FieldNum(ar.Length - 1) As Integer
                For i As Integer = 0 To FieldNum.Length - 1
                    FieldNum(i) = Array.IndexOf(Hdr2.fieldName, ar(i))
                Next

                Dim Block As PDoxDataBlock = Nothing
                Dim BlockNum As Integer = 0
                Dim FieldVal(Hdr2.fieldName.Length - 1) As String
                NumRec = 0

                While sr.Peek >= 0
                    Array.Clear(FieldVal, 0, FieldVal.Length)
                    ar = sr.ReadLine.Split(vbTab)
                    For i As Integer = 0 To ar.Length - 1
                        If i <= FieldNum.Length - 1 AndAlso FieldNum(i) <> -1 Then FieldVal(FieldNum(i)) = ar(i)
                    Next
                    If AutoIncStart <> 0 Then
                        FieldVal(0) = AutoIncStart
                        AutoIncStart += 1
                    End If
                    If Not PutNextRecord(bw, Hdr, Hdr2, Block, BlockNum, FieldVal) Then Exit While
                    NumRec += 1
                    'If Cancelled Then Return False
                    'MyBase.RaiseProgress(50, 100, String.Format("Importing record {0}...", NumRec))
                End While
                'write last block
                If Block.fileData IsNot Nothing Then WriteBlock(bw, Hdr, Block, .fileBlocks + 1, True)

                'overwrite header
                PutHeader(bw, Hdr, Hdr2)

            End With
            Return True
        Catch ex As Exception
            Throw New DbSqlExceptionPDox(String.Format("Unable to import to Paradox file ({0}); the file may be corrupted.", ImportFileName), ex)
            Return False
        Finally
            If sr IsNot Nothing Then
                sr.Close()
                sr.Dispose()
                br.Close()
                bw.Close()
                fs.Close()
                fs.Dispose()
            End If
            Hdr = Nothing
            Hdr2 = Nothing
            'MyBase.RaiseProgress(100, 100, "")
        End Try

    End Function

    ''' <summary>
    ''' Create tab-delimited text file and export table to it
    ''' </summary>
    ''' <param name="TableName">Name of table to export</param>
    ''' <param name="ExportFileName">Name of file to export to (if blank, will export to temp file and return name here; caller must place in clipboard and delete tempfile)</param>
    ''' <returns>True if export successful</returns>
    ''' <remarks>Override for Paradox</remarks>
    Public Overrides Function ExportTable(ByVal TableName As String, ByRef ExportFileName As String, Optional ByVal SQLString As String = "") As Boolean

        Dim fs As IO.FileStream = Nothing
        Dim br As IO.BinaryReader = Nothing
        Dim sw As IO.StreamWriter = Nothing
        Dim Hdr As PDoxHeader = Nothing
        Dim Hdr2 As PDoxHeader2 = Nothing

        If SQLString <> "" Then Return MyBase.ExportTable(TableName, ExportFileName, SQLString)

        'Dim NumRecords As Long = Me.GetRecordCount(TableName)

        Dim PDoxFileName As String = Connection.DataSource & "\" & RemoveBrackets(TableName) & ".db"

        Return ExportToText(PDoxFileName, ExportFileName)

        'Try
        '    Cancelled = False
        '    If Not My.Computer.FileSystem.FileExists(PDoxFileName) Then
        '        Throw New IO.FileNotFoundException("File not found", PDoxFileName)
        '        Return False
        '    End If

        '    fs = New IO.FileStream(PDoxFileName, IO.FileMode.Open, IO.FileAccess.Read, IO.FileShare.Read, 1024)
        '    br = New IO.BinaryReader(fs)

        '    If ExportFileName = "" Then ExportFileName = My.Computer.FileSystem.GetTempFileName

        '    sw = New IO.StreamWriter(ExportFileName, False)

        '    'read header

        '    If Not GetHeader(br, Hdr, Hdr2) Then Return False

        '    With Hdr2

        '        'write column headings and read each data block

        '        For f As Integer = 0 To Hdr.numFields - 1
        '            If f > 0 Then sw.Write(vbTab)
        '            sw.Write(.fieldName(f))
        '        Next
        '        sw.WriteLine()

        '        Dim Block As PDoxDataBlock = Nothing
        '        Dim BlockNum As Integer = 0
        '        Dim RecNum As Integer = 0

        '        While Hdr.fileBlocks > 0 AndAlso GetNextRecord(br, Hdr, Hdr2, Block, BlockNum)
        '            For f As Integer = 0 To mItems.Count - 1
        '                If f > 0 Then sw.Write(vbTab)
        '                If TypeOf mItems(f) Is Date Then
        '                    sw.Write(String.Format("{0:MM/dd/yyyy HH:mm}", mItems(f)))
        '                ElseIf TypeOf mItems(f) Is Boolean Then
        '                    sw.Write(IIf(mItems(f), "1", "0"))
        '                Else
        '                    sw.Write(mItems(f))
        '                End If
        '            Next
        '            sw.WriteLine()
        '            RecNum += 1

        '            MyBase.RaiseProgress(RecNum, Hdr.numRecords, String.Format("Exporting record {0} of {1} from {2}...", RecNum, Hdr.numRecords, TableName))
        '            If Cancelled Then Return False
        '        End While
        '    End With
        '    Return True
        'Catch ex As Exception
        '    ExportTable = False
        '    Throw New ApplicationException(String.Format("Unable to export from Paradox file ({0}); the file may be corrupted.", PDoxFileName), ex)
        'Finally
        '    If sw IsNot Nothing Then
        '        sw.Close()
        '        sw.Dispose()
        '        br.Close()
        '        fs.Close()
        '        fs.Dispose()
        '    End If
        '    Hdr = Nothing
        '    Hdr2 = Nothing
        'End Try
    End Function

    Public Shared Function ExportToText(ByVal PDoxFileName As String, ByRef ExportFileName As String) As Boolean

        Dim fs As IO.FileStream = Nothing
        Dim br As IO.BinaryReader = Nothing
        Dim sw As IO.StreamWriter = Nothing
        Dim Hdr As PDoxHeader = Nothing
        Dim Hdr2 As PDoxHeader2 = Nothing

        Try
            'Cancelled = False
            If Not My.Computer.FileSystem.FileExists(PDoxFileName) Then
                Throw New IO.FileNotFoundException("File not found", PDoxFileName)
                Return False
            End If

            fs = New IO.FileStream(PDoxFileName, IO.FileMode.Open, IO.FileAccess.Read, IO.FileShare.Read, 1024)
            br = New IO.BinaryReader(fs)

            If ExportFileName = "" Then ExportFileName = My.Computer.FileSystem.GetTempFileName

            sw = New IO.StreamWriter(ExportFileName, False)

            'read header

            If Not GetHeader(br, Hdr, Hdr2) Then Return False

            With Hdr2

                'write column headings and read each data block

                For f As Integer = 0 To Hdr.numFields - 1
                    If f > 0 Then sw.Write(vbTab)
                    sw.Write(.fieldName(f))
                Next
                sw.WriteLine()

                Dim Block As PDoxDataBlock = Nothing
                Dim BlockNum As Integer = 0
                Dim RecNum As Integer = 0

                While Hdr.fileBlocks > 0 AndAlso GetNextRecord(br, Hdr, Hdr2, Block, BlockNum)
                    For f As Integer = 0 To mItems.Count - 1
                        If f > 0 Then sw.Write(vbTab)
                        If TypeOf mItems(f) Is Date Then
                            sw.Write(String.Format("{0:MM/dd/yyyy HH:mm}", mItems(f)))
                        ElseIf TypeOf mItems(f) Is Boolean Then
                            sw.Write(IIf(mItems(f), "1", "0"))
                        Else
                            sw.Write(mItems(f))
                        End If
                    Next
                    sw.WriteLine()
                    RecNum += 1

                    'MyBase.RaiseProgress(RecNum, Hdr.numRecords, String.Format("Exporting record {0} of {1} from {2}...", RecNum, Hdr.numRecords, TableName))
                    'If Cancelled Then Return False
                End While
            End With
            Return True
        Catch ex As Exception
            Throw New ApplicationException(String.Format("Unable to export from Paradox file ({0}); the file may be corrupted.", PDoxFileName), ex)
            Return False
        Finally
            If sw IsNot Nothing Then
                sw.Close()
                sw.Dispose()
                br.Close()
                fs.Close()
                fs.Dispose()
            End If
            Hdr = Nothing
            Hdr2 = Nothing
        End Try
    End Function

    ''' <summary>
    ''' Determine the number of records in a table
    ''' </summary>
    ''' <param name="TableName">Name of table</param>
    ''' <returns>Number of records</returns>
    ''' <remarks>Must override for Paradox</remarks>
    Public Overrides Function GetRecordCount(ByVal TableName As String, Optional ByVal CommandTimeout As Integer = 10) As Integer
        Dim subdir As String = Connection.DataSource
        If Not subdir.EndsWith("\") Then subdir &= "\"
        Return modParadox.NumRecords(String.Format("{0}{1}.db", subdir, RemoveBrackets(TableName)))
    End Function

    Public Overrides Function GetColumnList(ByVal TableName As String) As System.Collections.Generic.List(Of String)
        Dim fs As IO.FileStream = Nothing
        Dim br As IO.BinaryReader = Nothing
        Dim Hdr As PDoxHeader = Nothing
        Dim Hdr2 As PDoxHeader2 = Nothing
        Dim PDoxFileName As String = Connection.DataSource & "\" & RemoveBrackets(TableName) & ".db"
        Dim ColList As New Generic.List(Of String)

        Try
            If Not My.Computer.FileSystem.FileExists(PDoxFileName) Then
                Throw New IO.FileNotFoundException("File not found", PDoxFileName)
                Return Nothing
            End If

            fs = New IO.FileStream(PDoxFileName, IO.FileMode.Open, IO.FileAccess.Read, IO.FileShare.Read, 1024)
            br = New IO.BinaryReader(fs)

            'read header

            If Not GetHeader(br, Hdr, Hdr2) Then Return Nothing

            With Hdr2
                For f As Integer = 0 To Hdr.numFields - 1
                    ColList.Add(.fieldName(f).ToUpper)
                Next
            End With
            Return ColList
        Catch ex As Exception
            Throw New ApplicationException(String.Format("Unable to open Paradox file ({0}); the file may be in use by another application or is corrupted.", PDoxFileName), ex)
            Return Nothing
        Finally
            If br IsNot Nothing Then
                br.Close()
                fs.Close()
                fs.Dispose()
            End If
            Hdr = Nothing
            Hdr2 = Nothing
        End Try
    End Function

    ''' <summary>
    ''' Capitalize first character and any characters following a space,|,\,;,.,_,(
    ''' </summary>
    Public Overloads Function Proper(ByVal s As String) As String
        Dim ar() As Char = s.ToLower.ToCharArray
        For i As Integer = 0 To ar.Length - 1
            If i = 0 OrElse " |\;_(".Contains(ar(i - 1)) Then ar(i) = Char.ToUpper(ar(i))
        Next
        Return New String(ar)
    End Function

End Class

' ''' <summary>
' ''' Paradox provider (does not require BDE)
' ''' </summary>
' ''' <remarks>
' ''' Although this relies on OleDB, the BDE is NOT required; my own library is used to export from .DB to .TXT without BDE and from then on .TXT files are used
' ''' </remarks>
'Public Class clsDBParadoxNoBDE
'    Inherits clsDBText

'    Private OrigDataSource As String = ""

'    Public Sub New()
'        DatabaseType = enumDBType.Paradox
'    End Sub

'    ''' <summary>
'    ''' Creates a provider-specific connection object given previously specified properties as required (DataSource, DatabaseName, UserName, Password, etc.)
'    ''' Note that the connection is normally automatically created when you instantiate the class
'    ''' </summary>
'    Public Overrides Sub CreateConnection()
'        'datasource initially set to that of paradox tables
'        Dim dbPDox As New clsDBParadox
'        dbPDox.DataSource = DataSource
'        For Each tbl As String In dbPDox.GetTableList
'            ExportIfNewer()
'        Next
'        Dim csb As New OleDbConnectionStringBuilder
'        With csb
'            .Provider = "Microsoft.Jet.OLEDB.4.0"

'            'copy all .db files to .txt files and use temp dir it as data source directory
'            OrigDataSource = DataSource
'            Dim NewDataSource As String = TempPath & "\Paradox"
'            If My.Computer.FileSystem.DirectoryExists(NewDataSource) Then My.Computer.FileSystem.DeleteDirectory(NewDataSource, FileIO.DeleteDirectoryOption.DeleteAllContents)
'            My.Computer.FileSystem.CreateDirectory(NewDataSource)
'            Dim ExportError As Boolean = False

'            'set up temporary connection so can export db files
'            .DataSource = OrigDataSource
'            Connection = New OleDbConnection(.ConnectionString)
'            .Item("Extended Properties") = "Paradox 7.x"
'            For Each fn As String In My.Computer.FileSystem.GetFiles(OrigDataSource, FileIO.SearchOption.SearchTopLevelOnly, New String() {"*.db"})
'                Dim TableName As String = IO.Path.GetFileNameWithoutExtension(fn)
'                Dim TextFile As String = String.Format("{0}\{1}.txt", NewDataSource, TableName)
'                If Not ExportTable(TableName, TextFile) Then
'                    ExportError = True
'                    Exit For
'                End If
'            Next
'            If Not ExportError Then
'                Me = New clsDBText()
'                .Item("Extended Properties") = "Text;HDR=YES;FMT=Delimited"
'                DataSource = NewDataSource
'                AvoidBDE = True
'            End If
'            .DataSource = DataSource 'should be working directory; must reset when doing queries on support or master tables
'            Connection = New OleDbConnection(.ConnectionString)
'        End With
'    End Sub

'    ''' <summary>
'    ''' Copy file from one location to another if the destination is older or doesn't exist
'    ''' </summary>
'    ''' <param name="SourceFile">Full path and filename of file to copy</param>
'    ''' <param name="DestFile">Full path and filename of destination file</param>
'    ''' <returns>True if copy successful or no need to update; false if error occurred</returns>
'    Private Function ExportIfNewer(dbPDox As clsDBParadox, ByVal TableName As String) As Boolean
'        Dim SourceFile As String = dbPDox.DataSource & "\"
'        If Not My.Computer.FileSystem.FileExists(SourceFile) Then Throw New ApplicationException("Source file not found: " & SourceFile) : Return False
'        Dim MustUpdate As Boolean = False
'        If Not My.Computer.FileSystem.FileExists(DestFile) Then
'            MustUpdate = True
'        Else
'            Dim SourceDate As Date = IO.File.GetLastWriteTime(SourceFile)
'            Dim DestDate As Date = IO.File.GetLastWriteTime(DestFile)
'            MustUpdate = SourceDate > DestDate
'        End If
'        If MustUpdate Then
'            Try
'                My.Computer.FileSystem.CopyFile(SourceFile, DestFile, True)
'            Catch ex As Exception
'                Throw New ApplicationException(String.Format("Unable to copy file {0} to {1}", SourceFile, DestFile, ex))
'                Return False
'            End Try
'        End If
'        Return True
'    End Function

'End Class

Public Class DbSqlExceptionPDox
    Inherits DbSqlException

    Public Sub New(ByVal ErrorMsg As String, ByRef ex As Exception)
        MyBase.New(ErrorMsg, ex)
    End Sub

    Public Sub New(ByVal ErrorMsg As String, ByVal SQLString As String)
        MyBase.New(String.Format("{0}; this error may be caused by a corrupted table; you should be able to rebuild the table using File | Explore Database | Compact/Repair. The SQL string was: {1}", ErrorMsg, SQLString))
    End Sub

    Public Sub New(ByVal ErrorMsg As String, ByVal SQLString As String, ByRef ex As Exception)
        MyBase.New(String.Format("{0}; this error may be caused by a corrupted table; you should be able to rebuild the table using File | Explore Database | Compact/Repair. The SQL string was: {1}", ErrorMsg, SQLString), ex)
    End Sub
End Class
